using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;

using log4net;

namespace InformationSchema
{
    public class DatabaseHelper
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(DatabaseHelper));

        public static DbDataReader ExecuteReader(string command_text)
        {
            return ExecuteReader(GetConnectionString(), command_text);
        }

        public static DbDataReader ExecuteReader(string connection_string, string command_text)
        {
            DbConnection connection = new OdbcConnection(connection_string);
            DbCommand command = new OdbcCommand(command_text, (OdbcConnection)connection);
            command.CommandType = CommandType.Text;

            try
            {
                DateTime start_time = DateTime.Now;
                connection.Open();

                DbDataReader transient = command.ExecuteReader(CommandBehavior.CloseConnection);

                DateTime end_time = DateTime.Now;
                TimeSpan time_diff = end_time - start_time;
                log.DebugFormat("SQL ({0}): {1}", time_diff.TotalMilliseconds, command_text);

                return transient;
            }
            catch (OdbcException sql_exception)
            {
                connection.Close();
                log.ErrorFormat("ODBC Exception: {0}", sql_exception.Message);
                throw;
            }
            catch (DbException sql_exception)
            {
                connection.Close();
                log.ErrorFormat("DB Exception: {0}", sql_exception.Message);
                throw;
            }
            catch (Exception exception)
            {
                connection.Close();
                log.ErrorFormat("Exception: {0}", exception.Message);
                throw;
            }

        }

        public static DataSet ExecuteCommand(string command_text)
        {
            return ExecuteCommand(GetConnectionString(), command_text);
        }

        public static DataSet ExecuteCommand(string connection_string, string command_text)
        {

            using (DbConnection connection = new OdbcConnection(connection_string))
            {
                connection.Open();

                DbCommand command = new OdbcCommand(command_text, (OdbcConnection)connection);
                command.CommandType = CommandType.Text;

                DbDataAdapter adapter = new OdbcDataAdapter((OdbcCommand)command);
                DataSet result = new DataSet();
                try
                {
                    adapter.Fill(result);
                }
                catch (OdbcException sql_exception)
                {
                    log.ErrorFormat("SQL Exception: {0}", sql_exception.Message);
                    throw;
                }
                catch (DbException sql_exception)
                {
                    log.ErrorFormat("DB Exception: {0}", sql_exception.Message);
                    throw;
                }
                catch (Exception exception)
                {
                    log.ErrorFormat("Exception: {0}", exception.Message);
                    throw;
                }

                return result;
            }
        }

        public static string GetConnectionString()
        {
            string environment = string.Empty;
            string connection_string = string.Empty;

            environment = ConfigurationSettings.AppSettings.Get("environment");

            switch (environment)
            {
                case "development":
                    // do something
                    connection_string = ConfigurationSettings.AppSettings.Get("development");
                    break;
                case "production":
                    // do something
                    connection_string = ConfigurationSettings.AppSettings.Get("production");
                    break;
                default:
                    connection_string = "DSN=test1;";
                    break;
            }

            return connection_string;
        }

        public static object ExecuteScalar(string connection_string, string command_text)
        {
            using (OdbcConnection connection = new OdbcConnection(connection_string))
            {
                OdbcCommand command = new OdbcCommand(command_text, connection);
                command.CommandType = CommandType.Text;

                try
                {
                    connection.Open();

                    return command.ExecuteScalar();
                }
                catch (OdbcException sql_exception)
                {
                    connection.Close();
                    log.ErrorFormat("SQL Exception: {0}", sql_exception.Message);
                    throw;
                }
                catch (Exception exception)
                {
                    connection.Close();
                    log.ErrorFormat("Exception: {0}", exception.Message);
                    throw;
                }

            }
        }

    }

}
